﻿using Donal.Xms.Common;
using Donal.Xms.DBUtility;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Reflection;
using System.Text;


namespace Donal.Xms.DAL
{
    /// <summary>
    /// 数据访问类:订单表
    /// </summary>
    public partial class orders
    {
        private string databaseprefix; //数据库表名前缀
        public orders(string _databaseprefix)
        {
            databaseprefix = _databaseprefix;
        }

        #region 基本方法================================
        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(int id)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from  " + databaseprefix + "orders");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(Model.orders model)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        #region 主表信息==========================
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();//数据字段
                        StringBuilder str2 = new StringBuilder();//数据参数
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        List<SqlParameter> paras = new List<SqlParameter>();
                        strSql.Append("insert into " + databaseprefix + "orders(");
                        foreach (PropertyInfo pi in pros)
                        {
                            //如果不是主键则追加sql字符串
                            if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                            {
                                //判断属性值是否为空
                                if (pi.GetValue(model, null) != null)
                                {
                                    str1.Append(pi.Name + ",");//拼接字段
                                    str2.Append("@" + pi.Name + ",");//声明参数
                                    paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                                }
                            }
                        }
                        strSql.Append(str1.ToString().Trim(','));
                        strSql.Append(") values (");
                        strSql.Append(str2.ToString().Trim(','));
                        strSql.Append(") ");
                        strSql.Append(";select @@IDENTITY;");
                        object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                        model.id = Convert.ToInt32(obj);
                        #endregion

                        #region 订单商品列表======================
                        if (model.order_goods != null)
                        {
                            StringBuilder strSql2; //SQL字符串
                            StringBuilder strSql3;
                            StringBuilder strSql4;
                            StringBuilder str21; //数据库字段
                            StringBuilder str22; //声明参数
                            foreach (Model.order_goods modelt in model.order_goods)
                            {
                                strSql2 = new StringBuilder();
                                str21 = new StringBuilder();
                                str22 = new StringBuilder();
                                PropertyInfo[] pros2 = modelt.GetType().GetProperties();
                                List<SqlParameter> paras2 = new List<SqlParameter>();
                                strSql2.Append("insert into " + databaseprefix + "order_goods(");
                                foreach (PropertyInfo pi in pros2)
                                {
                                    if (!pi.Name.Equals("id"))
                                    {
                                        if (pi.GetValue(modelt, null) != null)
                                        {
                                            str21.Append(pi.Name + ",");
                                            str22.Append("@" + pi.Name + ",");
                                            if (pi.Name.Equals("order_id"))
                                            {
                                                paras2.Add(new SqlParameter("@" + pi.Name, model.id));//将刚插入的父ID赋值
                                            }
                                            else
                                            {
                                                paras2.Add(new SqlParameter("@" + pi.Name, pi.GetValue(modelt, null)));
                                            }
                                        }
                                    }
                                }
                                strSql2.Append(str21.ToString().Trim(','));
                                strSql2.Append(") values (");
                                strSql2.Append(str22.ToString().Trim(','));
                                strSql2.Append(") ");
                                DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), paras2.ToArray()); 
                            }
                        }
                        #endregion

                        #region 删除购物车中的商品

                        #endregion

                        trans.Commit(); //提交事务
                    }
                    catch
                    {
                        trans.Rollback(); //回滚事务
                        conn.Close();
                        return 0;
                    }
                }
                conn.Close();
            }
            return model.id;
        }


        /// <summary>
        /// 增加一条数据
        /// </summary>
        public int Add(SqlConnection conn, SqlTransaction trans, Model.orders model)
        {
            try
            {
                #region 主表信息==========================
                StringBuilder strSql = new StringBuilder();
                StringBuilder str1 = new StringBuilder();//数据字段
                StringBuilder str2 = new StringBuilder();//数据参数
                                                         //利用反射获得属性的所有公共属性
                PropertyInfo[] pros = model.GetType().GetProperties();
                List<SqlParameter> paras = new List<SqlParameter>();
                strSql.Append("insert into " + databaseprefix + "orders(");
                foreach (PropertyInfo pi in pros)
                {
                    //如果不是主键则追加sql字符串
                    if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                    {
                        //判断属性值是否为空
                        if (pi.GetValue(model, null) != null)
                        {
                            str1.Append(pi.Name + ",");//拼接字段
                            str2.Append("@" + pi.Name + ",");//声明参数
                            paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                        }
                    }
                }
                strSql.Append(str1.ToString().Trim(','));
                strSql.Append(") values (");
                strSql.Append(str2.ToString().Trim(','));
                strSql.Append(") ");
                strSql.Append(";select top 1 id from " + databaseprefix + "orders where order_no='" + model.order_no + "'");
                object obj = DbHelperSQL.GetSingle(conn, trans, strSql.ToString(), paras.ToArray());//带事务
                model.id = Convert.ToInt32(obj);

                #endregion

                #region 订单商品列表======================
                if (model.order_goods != null)
                {
                    StringBuilder strSql2; //SQL字符串                    
                    StringBuilder str21; //数据库字段
                    StringBuilder str22; //声明参数

                    foreach (Model.order_goods modelt in model.order_goods)
                    {
                        strSql2 = new StringBuilder();
                        str21 = new StringBuilder();
                        str22 = new StringBuilder();
                        PropertyInfo[] pros2 = modelt.GetType().GetProperties();
                        List<SqlParameter> paras2 = new List<SqlParameter>();
                        strSql2.Append("insert into " + databaseprefix + "order_goods(");
                        foreach (PropertyInfo pi in pros2)
                        {
                            if (!pi.Name.Equals("id"))
                            {
                                if (pi.GetValue(modelt, null) != null)
                                {
                                    str21.Append(pi.Name + ",");
                                    str22.Append("@" + pi.Name + ",");
                                    if (pi.Name.Equals("order_id"))
                                    {
                                        paras2.Add(new SqlParameter("@" + pi.Name, model.id));//将刚插入的父ID赋值
                                    }
                                    else
                                    {
                                        paras2.Add(new SqlParameter("@" + pi.Name, pi.GetValue(modelt, null)));
                                    }
                                }
                            }
                        }
                        strSql2.Append(str21.ToString().Trim(','));
                        strSql2.Append(") values (");
                        strSql2.Append(str22.ToString().Trim(','));
                        strSql2.Append(") ");
                        DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), paras2.ToArray());
                    }
                }
                #endregion
            }
            catch (Exception e)
            {
                Utils.WriteLogs("保存订单时异常", "异常：" + e.Message + " " + e.StackTrace, "", DTKeys.EXCEPION_PATH, model.order_no + "_异常");

                return 0;
            }

            return model.id;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(SqlConnection conn, SqlTransaction trans, Model.orders model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update " + databaseprefix + "orders set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), paras.ToArray()) > 0;
        }

        /// <summary>
        /// 更新一条数据
        /// </summary>
        public bool Update(Model.orders model)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            List<SqlParameter> paras = new List<SqlParameter>();
            strSql.Append("update " + databaseprefix + "orders set ");
            foreach (PropertyInfo pi in pros)
            {
                //如果不是主键则追加sql字符串
                if (!pi.Name.Equals("id") && !typeof(System.Collections.IList).IsAssignableFrom(pi.PropertyType))
                {
                    //判断属性值是否为空
                    if (pi.GetValue(model, null) != null)
                    {
                        str1.Append(pi.Name + "=@" + pi.Name + ",");//声明参数
                        paras.Add(new SqlParameter("@" + pi.Name, pi.GetValue(model, null)));//对参数赋值
                    }
                }
            }
            strSql.Append(str1.ToString().Trim(','));
            strSql.Append(" where id=@id");
            paras.Add(new SqlParameter("@id", model.id));
            return DbHelperSQL.ExecuteSql(strSql.ToString(), paras.ToArray()) > 0;
        }


        public bool UpdateAddressForChildOrder(Model.orders model)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.Append("update " + databaseprefix + "orders set ");
            strSql.Append("accepte_name ='" + model.accepte_name + "', ");
            strSql.Append("accept_address  ='" + model.accept_address + "', ");
            strSql.Append("accepte_mobile  ='" + model.accepte_mobile + "', ");
            strSql.Append("express_title  ='" + "" + "'  ");
            strSql.Append("where parent_id = " + model.id + "  ");

            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }
                        

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {
            //删除订单商品
            List<CommandInfo> sqllist = new List<CommandInfo>();
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + databaseprefix + "order_goods where order_id=@order_id");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@order_id", SqlDbType.Int,10)};
            parameters2[0].Value = id;
            CommandInfo cmd = new CommandInfo(strSql2.ToString(), parameters2);
            sqllist.Add(cmd);

            //删除日志表
            StringBuilder strSql3 = new StringBuilder();
            strSql3.Append("delete from " + databaseprefix + "order_goods_logs where order_id=@order_id");
            SqlParameter[] parameters3 = {
                    new SqlParameter("@order_id", SqlDbType.Int,10)};
            parameters3[0].Value = id;
            cmd = new CommandInfo(strSql3.ToString(), parameters3);
            sqllist.Add(cmd);

            //删除操作日志表
            StringBuilder strSql4 = new StringBuilder();
            strSql4.Append("delete from " + databaseprefix + "logs where log_type = 1 and action_id=@order_id");
            SqlParameter[] parameters4 = {
                    new SqlParameter("@order_id", SqlDbType.Int,10)};
            parameters4[0].Value = id;
            cmd = new CommandInfo(strSql4.ToString(), parameters4);
            sqllist.Add(cmd);

            //删除订单主表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databaseprefix + "orders where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            cmd = new CommandInfo(strSql.ToString(), parameters);
            sqllist.Add(cmd);

            return DbHelperSQL.ExecuteSqlTran(sqllist) > 0;
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(SqlConnection conn, SqlTransaction trans, int id)
        {
            //删除订单商品           
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("delete from " + databaseprefix + "order_goods where order_id=@order_id");
            SqlParameter[] parameters2 = {
                    new SqlParameter("@order_id", SqlDbType.Int,10)};
            parameters2[0].Value = id;

            DbHelperSQL.ExecuteSql(conn, trans, strSql2.ToString(), parameters2);

            //删除订单主表
            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from " + databaseprefix + "orders where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;

            DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString(), parameters);

            return true;
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public string GetValue(string order_no, string fileName)
        {
            object obj = DbHelperSQL.GetSingle("select top 1 " + fileName + " from " + databaseprefix + "orders where order_no ='" + order_no + "' ");
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public string GetValue(int order_id, string fileName)
        {
            object obj = DbHelperSQL.GetSingle("select top 1 " + fileName + " from " + databaseprefix + "orders where id =" + order_id);
            if (obj != null)
            {
                return obj.ToString();
            }
            else
            {
                return "";
            }
        }

        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.orders GetModel(int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.orders model = new Model.orders();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                //拼接字段，忽略List<T>
                if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                {
                    str1.Append(p.Name + ",");//拼接字段
                }
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "orders");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public Model.orders GetModel(SqlConnection conn, SqlTransaction trans, int id)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.orders model = new Model.orders();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                //拼接字段，忽略List<T>
                if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                {
                    str1.Append(p.Name + ",");//拼接字段
                }
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(','));
            strSql.Append(" from " + databaseprefix + "orders");
            strSql.Append(" where id=@id");
            SqlParameter[] parameters = {
                    new SqlParameter("@id", SqlDbType.Int,10)};
            parameters[0].Value = id;
            DataTable dt = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters).Tables[0];

            if (dt.Rows.Count > 0)
            {
                return DataRowToModel(conn, trans, dt.Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" * ");
            strSql.Append(" FROM  " + databaseprefix + "orders ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * from (select (case  when order_type = 0 then 1 else case when order_type = 1 then 0 else order_type end end) order_type_order, * FROM " + databaseprefix + "orders) tab ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        /// <summary>
        /// 获得查询分页数据
        /// </summary>
        public DataSet GetDetailsList(int pageSize, int pageIndex, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM  view_order_details ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }

        public DataSet GetOrderGoodsList(string order_goods_ids, string order_ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM  view_order_goods_log_infor ");
            strSql.Append(" where order_id in( " + order_ids + ") and order_goods_id in(" + order_goods_ids + ")");
            return DbHelperSQL.Query(strSql.ToString());
        }
        /// <summary>
        /// 获得查询开票分页数据
        /// </summary>
        public DataSet GetInvoiceList(int pageSize, int pageIndex, int company_id, string strWhere, string filedOrder, out int recordCount)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM " + databaseprefix + "orders ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where user_name in(select user_name from dt_users where parent_id= " + company_id + ") " + strWhere);
            }
            recordCount = Convert.ToInt32(DbHelperSQL.GetSingle(PagingHelper.CreateCountingSql(strSql.ToString())));
            return DbHelperSQL.Query(PagingHelper.CreatePagingSql(recordCount, pageSize, pageIndex, strSql.ToString(), filedOrder));
        }
        #endregion

        #region 扩展方法================================

        /// <summary>
        /// 获得快递信息
        /// </summary>
        public DataSet GetExpressList(string order_ids)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select * FROM view_order_express_list ");
            if (order_ids.Trim() != "")
            {
                strSql.Append(" where order_id in( " + order_ids + ") ");
            }

            return DbHelperSQL.Query(strSql.ToString());
        }

        /// <summary>
        /// 是否存在该记录
        /// </summary>
        public bool Exists(string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from " + databaseprefix + "orders");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
                    new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;

            return DbHelperSQL.Exists(strSql.ToString(), parameters);
        }

        /// <summary>
        /// 根据订单号返回一个实体
        /// </summary>
        public Model.orders GetModel(string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.orders model = new Model.orders();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                //拼接字段，忽略List<T>
                if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                {
                    str1.Append(p.Name + ",");//拼接字段
                }
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
                    new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;

            DataSet ds = DbHelperSQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }


        public List<Model.orders> GetOrderList(int parent_id)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Model.orders> orders = new List<Model.orders>();
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();
                        Model.orders model = new Model.orders();
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        foreach (PropertyInfo p in pros)
                        {
                            //拼接字段，忽略List<T>
                            if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                            {
                                str1.Append(p.Name + ",");//拼接字段
                            }
                        }
                        strSql.Append("select " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
                        strSql.Append(" where parent_id=@parent_id");
                        SqlParameter[] parameters = {
                                        new SqlParameter("@parent_id", SqlDbType.Int,10)};
                        parameters[0].Value = parent_id;

                        DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters);

                        if (Utils.CheckDataSet(ds))
                        {
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Model.orders order = DataRowToModel(conn, trans, dr);
                                orders.Add(order);
                            }
                        }
                        else
                        {
                            return null;
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        conn.Close();
                        return null;
                    }

                    trans.Commit();
                    conn.Close();
                    return orders;
                }
            }
        }

        public List<Model.orders> GetOrderList(SqlConnection conn, SqlTransaction trans, int parent_id)
        {
            List<Model.orders> orders = new List<Model.orders>();
            try
            {
                StringBuilder strSql = new StringBuilder();
                StringBuilder str1 = new StringBuilder();
                Model.orders model = new Model.orders();
                //利用反射获得属性的所有公共属性
                PropertyInfo[] pros = model.GetType().GetProperties();
                foreach (PropertyInfo p in pros)
                {
                    //拼接字段，忽略List<T>
                    if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                    {
                        str1.Append(p.Name + ",");//拼接字段
                    }
                }
                strSql.Append("select " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
                strSql.Append(" where parent_id=@parent_id");
                SqlParameter[] parameters = {
                                        new SqlParameter("@parent_id", SqlDbType.Int,10)};
                parameters[0].Value = parent_id;

                DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters);

                if (Utils.CheckDataSet(ds))
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        Model.orders order = DataRowToModel(conn, trans, dr);
                        orders.Add(order);
                    }

                    return orders;
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }
        }

        public List<Model.orders> GetOrderList(string where)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    List<Model.orders> orders = new List<Model.orders>();
                    try
                    {
                        StringBuilder strSql = new StringBuilder();
                        StringBuilder str1 = new StringBuilder();
                        Model.orders model = new Model.orders();
                        //利用反射获得属性的所有公共属性
                        PropertyInfo[] pros = model.GetType().GetProperties();
                        foreach (PropertyInfo p in pros)
                        {
                            //拼接字段，忽略List<T>
                            if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                            {
                                str1.Append(p.Name + ",");//拼接字段
                            }
                        }
                        strSql.Append("select " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
                        strSql.Append(" where " + where);

                        DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());

                        if (Utils.CheckDataSet(ds))
                        {
                            foreach (DataRow dr in ds.Tables[0].Rows)
                            {
                                Model.orders order = DataRowToModel(conn, trans, dr);
                                orders.Add(order);
                            }
                        }
                        else
                        {
                            return null;
                        }
                    }
                    catch
                    {
                        trans.Rollback();
                        conn.Close();
                        return null;
                    }

                    trans.Commit();
                    conn.Close();
                    return orders;
                }
            }
        }

        public List<Model.orders> GetOrderList(SqlConnection conn, SqlTransaction trans, string where)
        {
            List<Model.orders> orders = new List<Model.orders>();
            try
            {
                StringBuilder strSql = new StringBuilder();
                StringBuilder str1 = new StringBuilder();
                Model.orders model = new Model.orders();
                //利用反射获得属性的所有公共属性
                PropertyInfo[] pros = model.GetType().GetProperties();
                foreach (PropertyInfo p in pros)
                {
                    //拼接字段，忽略List<T>
                    if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                    {
                        str1.Append(p.Name + ",");//拼接字段
                    }
                }
                strSql.Append("select " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
                strSql.Append(" where " + where);

                DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString());

                if (Utils.CheckDataSet(ds))
                {
                    foreach (DataRow dr in ds.Tables[0].Rows)
                    {
                        Model.orders order = DataRowToModel(conn, trans, dr);
                        orders.Add(order);
                    }
                }
                else
                {
                    return null;
                }
            }
            catch
            {
                return null;
            }

            return orders;
        }

        /// <summary>
        /// 根据订单号返回一个实体
        /// </summary>
        public Model.orders GetModel(SqlConnection conn, SqlTransaction trans, string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder str1 = new StringBuilder();
            Model.orders model = new Model.orders();
            //利用反射获得属性的所有公共属性
            PropertyInfo[] pros = model.GetType().GetProperties();
            foreach (PropertyInfo p in pros)
            {
                //拼接字段，忽略List<T>
                if (!typeof(System.Collections.IList).IsAssignableFrom(p.PropertyType))
                {
                    str1.Append(p.Name + ",");//拼接字段
                }
            }
            strSql.Append("select top 1 " + str1.ToString().Trim(',') + " from " + databaseprefix + "orders");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
                    new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;

            DataSet ds = DbHelperSQL.Query(conn, trans, strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                return DataRowToModel(conn, trans, ds.Tables[0].Rows[0]);
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据订单号获取支付方式ID
        /// </summary>
        public int GetPaymentId(string order_no)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 payment_id from " + databaseprefix + "orders");
            strSql.Append(" where order_no=@order_no");
            SqlParameter[] parameters = {
                    new SqlParameter("@order_no", SqlDbType.NVarChar,100)};
            parameters[0].Value = order_no;
            object obj = DbHelperSQL.GetSingle(strSql.ToString(), parameters);
            if (obj != null)
            {
                return Convert.ToInt32(obj);
            }
            return 0;
        }

        /// <summary>
        /// 返回数据总数
        /// </summary>
        public int GetCount(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(*) as H from " + databaseprefix + "orders ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 返回数据总数
        /// </summary>
        public int GetHoldUp()
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select top 1 holdup from " + databaseprefix + "configs ");

            return Convert.ToInt32(DbHelperSQL.GetSingle(strSql.ToString()));
        }

        /// <summary>
        /// 修改子订单状态，如果这个订单有子单
        /// </summary>
        public bool UpdateField(int parent_id, int status)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "orders set status = " + status);
            strSql.Append(" where parent_id=" + parent_id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "orders set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(strSql.ToString()) > 0;
        }


        /// <summary>
        /// 修改一列数据
        /// </summary>
        public bool UpdateField(SqlConnection conn, SqlTransaction trans, int id, string strValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update " + databaseprefix + "orders set " + strValue);
            strSql.Append(" where id=" + id);
            return DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString()) > 0;
        }

        /// <summary>
        /// 修改一列数据
        /// </summary>
        public string UpdateErpBuddyField(decimal order_amount, decimal user_amout, decimal user_pay_point, string companyCode, string operators, int _order_id, string orderNo, string remark)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string flowReslut = new cash_flow(databaseprefix).FlowAction(conn, trans, order_amount, user_amout, user_pay_point, companyCode, operators, _order_id, orderNo, remark);

                        if (flowReslut != "")
                        {
                            Utils.WriteLogs("财务审核订单通过，订单流水添加失败！", flowReslut + " " + orderNo + " " + remark, companyCode, DTKeys.EXCEPION_PATH, orderNo);
                            trans.Rollback(); //回滚事务
                            return flowReslut;
                        }

                        trans.Commit();
                        conn.Close();
                        return "";

                    }
                    catch (Exception e)
                    {
                        trans.Rollback(); //回滚事务
                        conn.Close();
                        return e.Message + e.StackTrace;
                    }
                }
            }
        }



        /// <summary>
        ///订单取消流水
        /// </summary>
        public string FlowActionCancelOrder(decimal order_amount, decimal user_pay_point, string companyCode, string operators, int _order_id, string orderNo, string remark, int flowType)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        string flowReslut = new cash_flow(databaseprefix).FlowActionCancelOrder(conn, trans, order_amount, user_pay_point, companyCode, operators, _order_id, orderNo, remark, flowType);

                        if (flowReslut != "")
                        {
                            Utils.WriteLogs("财务审核订单通过，订单流水添加失败！", flowReslut + " " + orderNo + " " + remark, companyCode, DTKeys.EXCEPION_PATH, orderNo);
                            trans.Rollback(); //回滚事务
                            return flowReslut;
                        }

                        trans.Commit();
                        conn.Close();
                        return "";

                    }
                    catch (Exception e)
                    {
                        trans.Rollback(); //回滚事务
                        conn.Close();
                        return e.Message + e.StackTrace;
                    }
                }
            }
        }

        /// <summary>
        /// 更新订单状态，并添加流水信息，添加订单日志等操作。
        /// </summary>
        /// <param name="order_no">订单号</param>
        /// <param name="payment">1、手机支付宝；2、电脑支付宝；3、月结；4、账户余额；5、微信公众号；6、微信扫码；
        /// 14、手机支付宝+账户余额；24、电脑支付宝+账户余额；54、微信公众号+账户余额；64、微信扫码+账户余额</param>
        /// <param name="strValue">更新的内容</param>
        /// <param name="order_amount">支付金额，0为订单金额！</param>
        public bool UpdateField(string order_no, string strValue, int payment, decimal order_amount = 0)
        {
            StringBuilder strSql = new StringBuilder();
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        Model.orders orders = GetModel(conn, trans, order_no);
                        if (order_amount == 0)
                        {
                            order_amount = orders.order_amount;
                        }
                        cash_flow cash_Flow = new cash_flow(databaseprefix);
                        string flowReslut = "";
                        string remark = "";
                        switch (payment)
                        {
                            case 1:
                                remark = "支付方式：手机支付宝";
                                break;
                            case 2:
                                remark = "支付方式：电脑支付宝";
                                break;
                            case 3:
                                remark = "支付方式：月结";
                                flowReslut = cash_Flow.FlowAction(conn, trans, -1 * order_amount,"", orders.user_name, orders.id, order_no, remark, 2, 1, true);
                                if (flowReslut != "")
                                {
                                    Utils.WriteLogs("更新月结流水失败！", flowReslut, "", DTKeys.EXCEPION_PATH, order_no);
                                    trans.Rollback(); //回滚事务
                                    conn.Close();
                                    return false;
                                }
                                break;
                            case 4:
                                remark = "支付方式：账户余额";
                                break;
                            case 5:
                                remark = "支付方式：微信公众号";
                                break;
                            case 6:
                                remark = "支付方式：微信扫码";
                                break;
                            case 14:
                                remark = "支付方式：账户余额+手机支付宝";
                                break;
                            case 24:
                                remark = "支付方式：账户余额+电脑支付宝";
                                break;
                            case 54:
                                remark = "支付方式：账户余额+微信公众号";
                                break;
                            case 64:
                                remark = "支付方式：账户余额+微信扫码";
                                break;
                        }

                        switch (payment)
                        {
                            case 14:
                            case 24:
                            case 54:
                            case 64:
                                //首先微信或支付宝线上支付成功的金额添加流水。
                                flowReslut = cash_Flow.FlowAction(conn, trans, order_amount, "", orders.user_name, orders.id, order_no, remark, 0, 7, false);
                                //然后用账户余额支付余下的订单金额，并生成流水。
                                flowReslut = cash_Flow.FlowAction(conn, trans, -1 * (orders.order_amount - order_amount), "", orders.user_name, orders.id, order_no, remark, 0, 1, true);
                                break;
                            case 4://使用余额支付购物需要更新会员余额
                                flowReslut = cash_Flow.FlowAction(conn, trans, -1 * order_amount, "", orders.user_name, orders.id, order_no, remark, 0, 1, true);
                                break;
                            default://其它支付购物不需要更新会员余额
                                flowReslut = cash_Flow.FlowAction(conn, trans, order_amount, "", orders.user_name, orders.id, order_no, remark, 0, 7, false);
                                break;
                        }

                        if (flowReslut != "")
                        {
                            Utils.WriteLogs("更新现金流水失败！", flowReslut, "", DTKeys.EXCEPION_PATH, order_no);
                            trans.Rollback(); //回滚事务
                            conn.Close();
                            return false;
                        }

                        strSql.Append("update " + databaseprefix + "orders set " + strValue);
                        strSql.Append(" where order_no='" + order_no + "'");

                        int r = DbHelperSQL.ExecuteSql(conn, trans, strSql.ToString());

                        new logs(databaseprefix).Add(conn, trans, new Model.logs()
                        {
                            action_id = orders.id,
                            log_type = 1,
                            operation_content = remark,
                            operation_time = DateTime.Now,
                            operation_type = "订单支付",
                            
                            user_type = orders.user_name
                        });

                        trans.Commit(); //提交事务
                        conn.Close();
                        return true;
                    }
                    catch (Exception e)
                    {
                        Utils.WriteLogs("处理异常！", "订单号：" + order_no + " " + e.Message + " " + e.StackTrace, DTKeys.SYSTEM_PATH, DTKeys.EXCEPION_PATH, order_no);
                        trans.Rollback(); //回滚事务
                        conn.Close();
                        return false;
                    }
                }
            }
        }

        
        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.orders DataRowToModel(DataRow row)
        {
            Model.orders model = new Model.orders();
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
                #endregion

                #region 子表信息======================
                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append("select * from " + databaseprefix + "order_goods");
                strSql1.Append(" where order_id=@id");
                SqlParameter[] parameters1 = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
                parameters1[0].Value = model.id;

                DataTable dt1 = DbHelperSQL.Query(strSql1.ToString(), parameters1).Tables[0];
                if (dt1.Rows.Count > 0)
                {
                    int rowsCount = dt1.Rows.Count;
                    List<Model.order_goods> models = new List<Model.order_goods>();
                    Model.order_goods modelt;
                    for (int n = 0; n < rowsCount; n++)
                    {
                        modelt = new Model.order_goods();
                        Type modeltType = modelt.GetType();
                        for (int i = 0; i < dt1.Rows[n].Table.Columns.Count; i++)
                        {
                            PropertyInfo proInfo = modeltType.GetProperty(dt1.Rows[n].Table.Columns[i].ColumnName);
                            if (proInfo != null && dt1.Rows[n][i] != DBNull.Value)
                            {
                                proInfo.SetValue(modelt, dt1.Rows[n][i], null);
                            }
                        }
                        models.Add(modelt);
                    }
                    model.order_goods = models;
                }
                #endregion
            }
            return model;
        }

        /// <summary>
        /// 将对象转换实体
        /// </summary>
        public Model.orders DataRowToModel(SqlConnection conn, SqlTransaction trans, DataRow row)
        {
            Model.orders model = new Model.orders();
            if (row != null)
            {
                #region 主表信息======================
                //利用反射获得属性的所有公共属性
                Type modelType = model.GetType();
                for (int i = 0; i < row.Table.Columns.Count; i++)
                {
                    //查找实体是否存在列表相同的公共属性
                    PropertyInfo proInfo = modelType.GetProperty(row.Table.Columns[i].ColumnName);
                    if (proInfo != null && row[i] != DBNull.Value)
                    {
                        proInfo.SetValue(model, row[i], null);//用索引值设置属性值
                    }
                }
                #endregion

                #region 子表信息======================
                StringBuilder strSql1 = new StringBuilder();
                strSql1.Append("select * from " + databaseprefix + "order_goods");
                strSql1.Append(" where order_id=@id");
                SqlParameter[] parameters1 = {
                        new SqlParameter("@id", SqlDbType.Int,10)};
                parameters1[0].Value = model.id;

                DataSet ds = DbHelperSQL.Query(conn, trans, strSql1.ToString(), parameters1);
                if (Utils.CheckDataSet(ds))
                {
                    DataTable dt1 = ds.Tables[0];
                    if (dt1.Rows.Count > 0)
                    {
                        int rowsCount = dt1.Rows.Count;
                        List<Model.order_goods> models = new List<Model.order_goods>();
                        Model.order_goods modelt;
                        for (int n = 0; n < rowsCount; n++)
                        {
                            modelt = new Model.order_goods();
                            Type modeltType = modelt.GetType();
                            for (int i = 0; i < dt1.Rows[n].Table.Columns.Count; i++)
                            {
                                PropertyInfo proInfo = modeltType.GetProperty(dt1.Rows[n].Table.Columns[i].ColumnName);
                                if (proInfo != null && dt1.Rows[n][i] != DBNull.Value)
                                {
                                    proInfo.SetValue(modelt, dt1.Rows[n][i], null);
                                }
                            }
                            models.Add(modelt);
                        }
                        model.order_goods = models;
                    }
                }

                #endregion

            }
            return model;
        }


        /// <summary>
        /// 获取最后下单时间
        /// </summary>
        public string GetLastOrderTime(int company_id)
        {
            DataSet ds = DbHelperSQL.Query("select add_time from dt_orders where id = (select max(id) from dt_orders  where user_id = " + company_id + "  and parent_id = 0 )");
            if (Utils.CheckDataSet(ds))
            {
                return ds.Tables[0].Rows[0][0].ToString();
            }
            else
            {
                return "";
            }
        }

        

        public bool AddTemp(List<Model.order_goods> order_goods_list)
        {
            using (SqlConnection conn = new SqlConnection(DbHelperSQL.connectionString))
            {
                conn.Open();//打开数据连接
                using (SqlTransaction trans = conn.BeginTransaction())
                {
                    try
                    {
                        order_goods ogdal = new order_goods(databaseprefix);
                        foreach (Model.order_goods og in order_goods_list)
                        {
                            ogdal.Update(og);
                        }

                        return true;
                    }
                    catch (Exception e)
                    {
                        return false;
                    }
                }
            }
        }
               

        /// <summary>
        /// 插入交易号，如果成功，返回真，说明没有处理过此订单。如果假，说明此订单正在处理。
        /// </summary>
        public bool AddTradeNo(string trade_no, string order_no, int payment, decimal order_amount,int notify)
        {
            try
            {
                DbHelperSQL.ExecuteSql("INSERT INTO [dbo].[dt_trade_nos]([trade_no],[order_no],[add_time],[payment],[order_amount],[notify]) VALUES('" + trade_no + "','" + order_no + "','" + DateTime.Now.ToString() + "'," + payment + "," + order_amount + "," + notify + ");");

                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 删除交易号
        /// </summary>
        /// <param name="trade_no">交易号</param>
        public void DeleteTradeNo(string trade_no)
        {
            DbHelperSQL.ExecuteSql("delete from dt_trade_nos where trade_no ='" + trade_no + "';");
        }

        #endregion
    }
}